﻿using Microsoft.EntityFrameworkCore;
using NewRetail.Application.Components.Goods.Entity;
using NewRetail.Application.Components.Goods.Model;
using NewRetail.Application.Components.Platform.Entity;
using NewRetail.Application.Components.Platform.Model;
using NewRetail.Application.Components.Warehouse.Entity;
using NewRetail.Application.Core;
using NewRetail.Application.Core.SingleStore;
using NewRetail.Application.Utility;
using OfficeOpenXml.Style;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;
using Volo.Abp.Domain.Repositories;

namespace NewRetail.Application.Components.Platform
{
    public class PosSalesFlowFilterDto : PagingRequestDto
    {
        public string warehouseId { get; set; }
        public string goodsId { get; set; }
        public string No { get; set; }
        public string cashierId { get; set; }

        public string goodsName { get; set; }
        public string cashierName { get; set; }

        public DateTime? startTime { get; set; }
        public DateTime? endTime { get; set; }

        public string flowNo { get; set; }
        public decimal? startSalePrice { get; set; }
        public decimal? endSalePrice { get; set; }
        //商品分类条件 add 20230111
        public string categoryID { get; set; }
        public string categoryNo { get; set; }
        public string categoryName { get; set; }
        //会员卡号或手机号
        public string memberCardOrTel { get; set; }
    }

    public interface IPosSalesFlowService
    {
        Task<PosSalesFlow[]> AttachCashRegister(PosSalesFlow[] data);
        Task<PosSalesFlow[]> AttachMember(PosSalesFlow[] data);
        Task<PosSalesFlow[]> AttachWarehouse(PosSalesFlow[] data);
        Task<PosSalesFlow[]> AttachCashier(PosSalesFlow[] data);
        Task<PosSalesFlow[]> AttachGoodsInfo(PosSalesFlow[] data);
        Task<PosSalesFlow[]> AttachGoodsCategory(PosSalesFlow[] data);
        Task<PosSalesFlow[]> AttachSupplier(PosSalesFlow[] data);

        #region add by cl 20221114
        /// <summary>
        /// 销售按商品汇总统计
        /// </summary>
        /// <param name="inputDto"></param>
        /// <returns></returns>
        Task<PagedResultDto<PosSaleSummaryStatisticsGoodShowDto>> GetPosSaleSummaryStatisticsGoodInfo(QueryPosSaleSummaryStatisticsGoodInputDto inputDto);
        /// <summary>
        /// 类别商品销售统计。针对门店的所有类别的销售统计
        /// </summary>
        /// <param name="inputDto"></param>
        /// <returns></returns>
        Task<IEnumerable<PosSaleCategorySummaryStatisticsGoodShowDto>> GetPosSaleCategorySummaryStatisticsGoodInfo(QueryPosSaleSummaryStatisticsGoodDto inputDto);
        /// <summary>
        /// 类别商品销售统计。针对收银员的所有类别的销售统计
        /// </summary>
        /// <param name="inputDto"></param>
        /// <returns></returns>
        Task<IEnumerable<PosSaleCashierCategorySummaryStatisticsGoodShowDto>> GetPosSaleCashierCategorySummaryStatisticsGoodInfo(QueryPosSaleSummaryStatisticsGoodDto inputDto);
        #endregion

        /// <summary>
        /// 导出销售流水数据
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        Stream ExportPosSalesFlowsInfo(PosSalesFlow[] data);
        /// <summary>
        /// 根据会员号或手机号获取会员信息
        /// </summary>
        /// <param name="memberCardOrTel"></param>
        /// <returns></returns>
        Task<PosMember[]> GetPosMemberInfosByCardOrTel(string memberCardOrTel);
    }

    public class PosSalesFlowService : MyApplicationService, IPosSalesFlowService
    {
        private readonly IRepository<PosSalesFlow> posSalesFlows;
        private readonly ISingleStoreService singleStoreService;

        public PosSalesFlowService(IRepository<PosSalesFlow> posSalesFlows, ISingleStoreService singleStoreService)
        {
            this.posSalesFlows = posSalesFlows;
            this.singleStoreService = singleStoreService;
        }

        public async Task<PosSalesFlow[]> AttachCashRegister(PosSalesFlow[] data)
        {
            var db = await this.posSalesFlows.GetDbContextAsync();

            await db.AttachData<PosCashRegister, PosSalesFlow>(data,
                idGetter: x => x.cashRegisterID,
                action: (m, arr) => m.PosCashRegister = arr.FirstOrDefault());

            return data;
        }

        public async Task<PosSalesFlow[]> AttachMember(PosSalesFlow[] data)
        {
            var db = await this.posSalesFlows.GetDbContextAsync();

            await db.AttachData<PosMember, PosSalesFlow>(data,
                idGetter: x => x.memberID,
                action: (m, arr) => m.PosMember = arr.FirstOrDefault());

            return data;
        }

        public async Task<PosSalesFlow[]> AttachWarehouse(PosSalesFlow[] data)
        {
            var db = await this.posSalesFlows.GetDbContextAsync();

            await db.AttachData<Warehouse.Entity.Warehouse, PosSalesFlow>(data,
                idGetter: x => x.warehouseID,
                action: (m, arr) => m.Warehouse = arr.FirstOrDefault());

            return data;
        }

        public async Task<PosSalesFlow[]> AttachGoodsCategory(PosSalesFlow[] data)
        {
            var db = await this.posSalesFlows.GetDbContextAsync();

            await db.AttachData<GoodsCategory, PosSalesFlow>(data,
                idGetter: x => x.categoryID,
                action: (m, arr) => m.GoodsCategory = arr.FirstOrDefault());

            return data;
        }

        public async Task<PosSalesFlow[]> AttachCashier(PosSalesFlow[] data)
        {
            var db = await this.posSalesFlows.GetDbContextAsync();

            await db.AttachData<PosCashier, PosSalesFlow>(data,
                idGetter: x => x.cashierID,
                action: (m, arr) => m.PosCashier = arr.FirstOrDefault());

            return data;
        }

        public async Task<PosSalesFlow[]> AttachGoodsInfo(PosSalesFlow[] data)
        {
            var db = await this.posSalesFlows.GetDbContextAsync();

            await db.AttachData<GoodsInfo, PosSalesFlow>(data,
                idGetter: x => x.goodsID,
                action: (m, arr) => m.GoodsInfo = arr.FirstOrDefault());

            return data;
        }

        #region 附加供应商 add by cl 20230111 
        public async Task<PosSalesFlow[]> AttachSupplier(PosSalesFlow[] data)
        {
            if (!data.Any())
            {
                return data;
            }

            string warehouseid = this.singleStoreService.CurrentStore;
            var warehouseGoodsIds = data.Select(x => $"{x.warehouseID}{x.goodsID}").ToArray();

            var db = await this.posSalesFlows.GetDbContextAsync();
            var inOutWarehousings = await this.QueryInOutWarehousingsByIds(warehouseGoodsIds);
            if (string.IsNullOrEmpty(warehouseid))
            {
                foreach (var m in data)
                {
                    m.Supplier = inOutWarehousings.Where(x => $"{m.warehouseID}{m.goodsID}".Contains($"{x.warehouseID}{x.goodsID}")).Select(s => s.Supplier).FirstOrDefault();
                }
            }
            else
            {
                foreach (var m in data)
                {
                    m.Supplier = inOutWarehousings.Where(x => $"{m.warehouseID}{m.goodsID}".Contains($"{x.warehouseID}{x.goodsID}") && x.warehouseID == warehouseid).Select(s => s.Supplier).FirstOrDefault();
                }
            }

            return data;
        }

        async Task<InOutWarehousing[]> QueryInOutWarehousingsByIds(string[] ids)
        {
            var db = await this.posSalesFlows.GetDbContextAsync();

            var inOutWarehousings = await db.Set<InOutWarehousing>().ToArrayAsync();
            inOutWarehousings = inOutWarehousings.Where(x => ids.Contains($"{x.warehouseID}{x.goodsID}")).ToArray();

            var suppliers = await db.Set<Supplier>().Where(x => inOutWarehousings.Select(x => x.supplierID).Contains(x.Id)).ToArrayAsync();

            foreach (var m in inOutWarehousings)
            {
                m.Supplier = suppliers.FirstOrDefault(x => x.Id == m.supplierID);
            }

            return inOutWarehousings;
        }

        #endregion

        #region add by cl 20221114

        GoodsCategory FindRoot(GoodsCategory[] data, string currentId)
        {
            var id = currentId;
            GoodsCategory categoryNode = null;

            var ids = new List<string>();

            while (true)
            {
                if (ids.Contains(id))
                {
                    break;
                }
                ids.Add(id);

                var node = data.FirstOrDefault(x => x.Id == id);
                if (node == null)
                {
                    break;
                }

                categoryNode = node;
                id = node.pID;
            }
            return categoryNode;
        }

        #region 销售按商品汇总统计
        /// <summary>
        /// 销售按商品汇总统计
        /// </summary>
        /// <param name="inputDto"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<PosSaleSummaryStatisticsGoodShowDto>> GetPosSaleSummaryStatisticsGoodInfo(QueryPosSaleSummaryStatisticsGoodInputDto inputDto)
        {
            var db = await this.posSalesFlows.GetDbContextAsync();
            var posSalesFlows = db.Set<PosSalesFlow>().AsNoTracking();
            posSalesFlows = posSalesFlows.WhereIf(inputDto.startTime != null, x => x.operateTime >= inputDto.startTime.Value);
            posSalesFlows = posSalesFlows.WhereIf(inputDto.endTime != null, x => x.operateTime < inputDto.endTime.Value);

            var warehouseGoods = db.Set<WarehouseGoods>().AsNoTracking();
            if (!string.IsNullOrWhiteSpace(inputDto.warehouseId))
            {
                posSalesFlows = posSalesFlows.Where(x => x.warehouseID == inputDto.warehouseId);
                warehouseGoods = warehouseGoods.Where(x => x.warehouseID == inputDto.warehouseId);
            }
            posSalesFlows = posSalesFlows.WhereIf(!string.IsNullOrWhiteSpace(inputDto.cashierId),
                x => x.cashierID == inputDto.cashierId);

            if (!string.IsNullOrWhiteSpace(inputDto.cashierName))
            {
                var cashierIds = db.Set<PosCashier>().AsNoTracking()
                    .Where(x => x.cashierName.Contains(inputDto.cashierName))
                    .Select(x => x.Id);
                posSalesFlows = posSalesFlows.Where(x => cashierIds.Contains(x.cashierID));
            }

            var goodsInfos = db.Set<GoodsInfo>().AsNoTracking();
            if (!string.IsNullOrWhiteSpace(inputDto.goodsName))
            {
                var goodsIds = goodsInfos
                    .Where(x => x.goodsName.Contains(inputDto.goodsName))
                    .Select(x => x.Id);
                posSalesFlows = posSalesFlows.Where(x => goodsIds.Contains(x.goodsID));
                warehouseGoods = warehouseGoods.Where(x => goodsIds.Contains(x.goodsID));
            }
            if (!string.IsNullOrWhiteSpace(inputDto.goodsNo))
            {
                var goodsIds = goodsInfos
                    .Where(x => x.No.Contains(inputDto.goodsNo))
                    .Select(x => x.Id);
                posSalesFlows = posSalesFlows.Where(x => goodsIds.Contains(x.goodsID));
                warehouseGoods = warehouseGoods.Where(x => goodsIds.Contains(x.goodsID));
            }
            var goodsCategorys = db.Set<GoodsCategory>().AsNoTracking();

            var result1 = await posSalesFlows.GroupJoin(goodsInfos, ct => ct.goodsID, cs => cs.Id, (x, y) => new { posSalesFlows = x, goodsInfos = y })
                .SelectMany(xy => xy.goodsInfos.DefaultIfEmpty(), (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfo = y })
                .GroupJoin(goodsCategorys, ct => ct.posSalesFlows.categoryID, cs => cs.Id, (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfos = x.goodsInfo, goodsCategorys = y })
                .SelectMany(xy => xy.goodsCategorys.DefaultIfEmpty(), (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfos = x.goodsInfos, goodsCategorys = y }).ToListAsync();
            var result = result1
                .GroupJoin(warehouseGoods, ct => $"{ct.posSalesFlows.warehouseID}{ct.posSalesFlows.goodsID}", cs => $"{cs.warehouseID}{cs.goodsID}", (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfos = x.goodsInfos, goodsCategorys= x.goodsCategorys, warehouseGoods = y })
                .SelectMany(xy => xy.warehouseGoods.DefaultIfEmpty(), (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfos = x.goodsInfos, goodsCategorys=x.goodsCategorys, warehouseGoods = y })
                .GroupBy(g => new
                {
                    g.posSalesFlows.warehouseID,
                    //g.posSalesFlows.cashierID,
                    g.posSalesFlows.categoryID,
                    g.posSalesFlows.goodsID
                })
                .Select(s => new PosSaleSummaryStatisticsGoodShowDto
                {
                    //categoryID = s.posSalesFlows.posSalesFlows.categoryID,
                    //categoryName = s.goodsCategorys.categoryName,
                    //categoryNo = s.goodsCategorys.No,
                    //goodsID = s.posSalesFlows.posSalesFlows.goodsID,
                    //goodsNo = s.posSalesFlows.goodInfos.No,
                    //goodsName = s.posSalesFlows.goodInfos.goodsName,
                    //saleQuantity = s.posSalesFlows.posSalesFlows.saleWay == SalesTypes.Sales ? s.posSalesFlows.posSalesFlows.saleQuantity : 0,
                    //saleMoney = s.posSalesFlows.posSalesFlows.saleWay == SalesTypes.Sales ? s.posSalesFlows.posSalesFlows.saleMoney : 0,
                    //reSaleQuantity = s.posSalesFlows.posSalesFlows.saleWay == SalesTypes.Refunds ? s.posSalesFlows.posSalesFlows.saleQuantity : 0,
                    //reSaleMoney = s.posSalesFlows.posSalesFlows.saleWay == SalesTypes.Refunds ? s.posSalesFlows.posSalesFlows.saleMoney : 0
                    warehouseID = s.Key.warehouseID,
                    //cashierID = s.Key.cashierID,
                    categoryID = s.Key.categoryID,
                    categoryName = s.Max(y => y.goodsCategorys.categoryName),
                    categoryNo = s.Max(y => y.goodsCategorys.No),
                    goodsID = s.Key.goodsID,
                    goodsNo = s.Max(y => y.goodsInfos.No),
                    goodsName = s.Max(y => y.goodsInfos.goodsName),
                    saleQuantity = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleQuantity : 0),
                    saleMoney = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleMoney : 0),
                    reSaleQuantity = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Refunds ? y.posSalesFlows.saleQuantity : 0),
                    reSaleMoney = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Refunds ? y.posSalesFlows.saleMoney : 0),
                    totalSaleMoney = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleMoney : -y.posSalesFlows.saleMoney),
                    reGdAmount = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Refunds ? y.posSalesFlows.gdMoney : 0),
                    totalGdSaleMoney = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleMoney : -y.posSalesFlows.gdMoney),
                    totalSaleQuantity = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleQuantity : -y.posSalesFlows.saleQuantity),
                    stockQuantity = s.Average(y => y.warehouseGoods.goodsNum)
                }).ToList();
            //result = result.GroupBy(g => new { g.categoryID, g.categoryName, g.categoryNo, g.goodsID, g.goodsNo, g.goodsName })
            //    .Select(s => new PosSaleSummaryStatisticsGoodShowDto
            //    {
            //        categoryID = s.Key.categoryID,
            //        categoryName = s.Key.categoryName,
            //        categoryNo = s.Key.categoryNo,
            //        goodsID = s.Key.goodsID,
            //        goodsNo = s.Key.goodsNo,
            //        goodsName = s.Key.goodsName,
            //        saleQuantity = s.Sum(y => y.saleQuantity),
            //        saleMoney = s.Sum(y => y.saleMoney),
            //        reSaleQuantity = s.Sum(y => y.reSaleQuantity),
            //        reSaleMoney = s.Sum(y => y.reSaleMoney),
            //        totalSaleMoney = s.Sum(y => y.saleQuantity) - s.Sum(y => y.reSaleQuantity),
            //        totalSaleQuantity = s.Sum(y => y.saleMoney) - s.Sum(y => y.reSaleMoney)
            //    }).ToList();

            //分类赋值
            var categoryData = await db.Set<GoodsCategory>().AsNoTracking().MaxTake().ToArrayAsync();
            //供应商赋值
            var suppliers = await db.Set<Supplier>().AsNoTracking().ToArrayAsync();
            //出入库
            var queryInOutWarehousing = db.Set<InOutWarehousing>().AsNoTracking();
            foreach (var m in result)
            {
                m.category = categoryData.FirstOrDefault(x => x.Id == m?.categoryID);
                m.rootCategory = FindRoot(categoryData, m?.categoryID);
                m.supplierId = queryInOutWarehousing.FirstOrDefault(x => x.warehouseID == m.warehouseID && x.goodsID == m.goodsID)?.supplierID;
            }
            //根据分类条件筛选
            if (!string.IsNullOrWhiteSpace(inputDto.categoryId))
            {
                result = result.Where(x => x.category?.Id == inputDto.categoryId).ToList();
            }

            await db.AttachData<Supplier, PosSaleSummaryStatisticsGoodShowDto>(result, x => x.supplierId,
                (m, arr) => m.Supplier = arr.FirstOrDefault());
            //根据供应商条件筛选
            if (!string.IsNullOrWhiteSpace(inputDto.supplierId))
            {
                result = result.Where(x => x.supplierId == inputDto.supplierId).ToList();
            }

            result = result.Where(x => x.goodsName != null).OrderBy(o=>o.categoryNo).ThenBy(o => o.supplierId).ThenBy(x => x.categoryName).ThenBy(x => x.goodsNo).ToList();

            var response = new PagedResultDto<PosSaleSummaryStatisticsGoodShowDto>();
            response.TotalCount = result.Count();

            int pageSize = inputDto.PageSize == 0 ? 100 : inputDto.PageSize, pageIndex = inputDto.Page == 0 ? 1 : inputDto.Page;
            //总页数
            var pageCount = (result.Count() + pageSize - 1) / pageSize;
            result = result.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();

            response.Items = result;

            //await db.AttachData<PosCashier, PosSaleSummaryStatisticsGoodShowDto>(result, x => x.cashierID,
            //    (m, arr) => m.PosCashier = arr.FirstOrDefault());
            await db.AttachData<Warehouse.Entity.Warehouse, PosSaleSummaryStatisticsGoodShowDto>(result, x => x.warehouseID,
                (m, arr) => m.Warehouse = arr.FirstOrDefault());

            return response;
        }
        #endregion

        #region 类别商品销售统计。针对门店的所有类别的销售统计
        /// <summary>
        /// 类别商品销售统计。针对门店的所有类别的销售统计
        /// </summary>
        /// <param name="inputDto"></param>
        /// <returns></returns>
        public async Task<IEnumerable<PosSaleCategorySummaryStatisticsGoodShowDto>> GetPosSaleCategorySummaryStatisticsGoodInfo(QueryPosSaleSummaryStatisticsGoodDto inputDto)
        {
            var db = await this.posSalesFlows.GetDbContextAsync();
            var posSalesFlows = db.Set<PosSalesFlow>().AsNoTracking();
            posSalesFlows = posSalesFlows.WhereIf(inputDto.startTime != null, x => x.operateTime >= inputDto.startTime.Value);
            posSalesFlows = posSalesFlows.WhereIf(inputDto.endTime != null, x => x.operateTime < inputDto.endTime.Value);

            var warehouseGoods = db.Set<WarehouseGoods>().AsNoTracking();

            if (!string.IsNullOrWhiteSpace(inputDto.warehouseId))
            {
                posSalesFlows = posSalesFlows.Where(x => x.warehouseID == inputDto.warehouseId);
                warehouseGoods = warehouseGoods.Where(x => x.warehouseID == inputDto.warehouseId);
            }
            posSalesFlows = posSalesFlows.WhereIf(!string.IsNullOrWhiteSpace(inputDto.cashierId),
                x => x.cashierID == inputDto.cashierId);

            if (!string.IsNullOrWhiteSpace(inputDto.cashierName))
            {
                var cashierIds = db.Set<PosCashier>().AsNoTracking()
                    .Where(x => x.cashierName.Contains(inputDto.cashierName))
                    .Select(x => x.Id);
                posSalesFlows = posSalesFlows.Where(x => cashierIds.Contains(x.cashierID));
            }

            var goodInfos = db.Set<GoodsInfo>().AsNoTracking();
            var goodsCategorys = db.Set<GoodsCategory>().AsNoTracking();

            var result1 = await posSalesFlows.GroupJoin(goodInfos, ct => ct.goodsID, cs => cs.Id, (x, y) => new { posSalesFlows = x, goodInfos = y })
                .SelectMany(xy => xy.goodInfos.DefaultIfEmpty(), (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfo = y }).ToListAsync();
            var result = result1
                .GroupJoin(warehouseGoods, ct => $"{ct.posSalesFlows.warehouseID}{ct.posSalesFlows.goodsID}", cs => $"{cs.warehouseID}{cs.goodsID}", (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfos = x.goodsInfo, warehouseGoods = y })//goodsCategorys = y })
                .SelectMany(xy => xy.warehouseGoods.DefaultIfEmpty(), (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfos = x.goodsInfos, warehouseGoods = y })//goodsCategorys = y })
                .GroupJoin(goodsCategorys, ct => ct.posSalesFlows.categoryID, cs => cs.Id, (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfos = x.goodsInfos, warehouseGoods = x.warehouseGoods, goodsCategorys = y })
                .SelectMany(xy => xy.goodsCategorys.DefaultIfEmpty(), (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfos = x.goodsInfos, warehouseGoods = x.warehouseGoods, goodsCategorys = y })
                .GroupBy(g => new
                {
                    g.posSalesFlows.warehouseID,
                    g.posSalesFlows.categoryID
                })
                .Select(s => new PosSaleCategorySummaryStatisticsGoodShowDto
                {
                    warehouseID = s.Key.warehouseID,
                    categoryID = s.Key.categoryID,
                    categoryName = s.Max(y => y.goodsCategorys?.categoryName),
                    categoryNo = s.Max(y => y.goodsCategorys?.No),
                    saleQuantity = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleQuantity : 0),
                    saleMoney = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleMoney : 0),
                    reSaleQuantity = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Refunds ? y.posSalesFlows.saleQuantity : 0),
                    reSaleMoney = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Refunds ? y.posSalesFlows.saleMoney : 0),
                    totalSaleMoney = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleMoney : -y.posSalesFlows.saleMoney),
                    reGdAmount = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Refunds ? y.posSalesFlows.gdMoney : 0),
                    totalGdSaleMoney = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleMoney : -y.posSalesFlows.gdMoney),
                    totalSaleQuantity = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleQuantity : -y.posSalesFlows.saleQuantity),
                    grossProfit = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Refunds ? 0 : y.posSalesFlows.salePrice - (y.warehouseGoods == null ? 0 : y.warehouseGoods.costPrice))
                }).ToList();

            result = result.Where(x => x.categoryName != null).OrderBy(x => x.warehouseID).ThenBy(x => x.categoryNo).ThenByDescending(x => x.totalSaleMoney).ToList();

            await db.AttachData<Warehouse.Entity.Warehouse, PosSaleCategorySummaryStatisticsGoodShowDto>(result, x => x.warehouseID,
                (m, arr) => m.Warehouse = arr.FirstOrDefault());

            //计算同店占比
            //所有金额小计的和
            var totalSaleMoneyHj = result.Sum(x => x.totalSaleMoney);
            var totalGdSaleMoneyHj = result.Sum(x => x.totalGdSaleMoney);
            foreach (var item in result)
            {
                item.warehouseRate = totalSaleMoneyHj == 0 ? 0 : Math.Round(UtilsHelper.ToDouble(item.totalSaleMoney, 0) / UtilsHelper.ToDouble(totalSaleMoneyHj, 0) * 100, 2, MidpointRounding.AwayFromZero);
                item.warehouseGdRate = totalGdSaleMoneyHj == 0 ? 0 : Math.Round(UtilsHelper.ToDouble(item.totalGdSaleMoney, 0) / UtilsHelper.ToDouble(totalGdSaleMoneyHj, 0) * 100, 2, MidpointRounding.AwayFromZero);
            }

            return result;
        }
        #endregion

        #region 类别商品销售统计。针对收银员的所有类别的销售统计
        /// <summary>
        /// 类别商品销售统计。针对收银员的所有类别的销售统计
        /// </summary>
        /// <param name="inputDto"></param>
        /// <returns></returns>
        public async Task<IEnumerable<PosSaleCashierCategorySummaryStatisticsGoodShowDto>> GetPosSaleCashierCategorySummaryStatisticsGoodInfo(QueryPosSaleSummaryStatisticsGoodDto inputDto)
        {
            var db = await this.posSalesFlows.GetDbContextAsync();
            var posSalesFlows = db.Set<PosSalesFlow>().AsNoTracking();
            posSalesFlows = posSalesFlows.WhereIf(inputDto.startTime != null, x => x.operateTime >= inputDto.startTime.Value);
            posSalesFlows = posSalesFlows.WhereIf(inputDto.endTime != null, x => x.operateTime < inputDto.endTime.Value);

            var warehouseGoods = db.Set<WarehouseGoods>().AsNoTracking();

            if (!string.IsNullOrWhiteSpace(inputDto.warehouseId))
            {
                posSalesFlows = posSalesFlows.Where(x => x.warehouseID == inputDto.warehouseId);
                warehouseGoods = warehouseGoods.Where(x => x.warehouseID == inputDto.warehouseId);
            }
            posSalesFlows = posSalesFlows.WhereIf(!string.IsNullOrWhiteSpace(inputDto.cashierId),
                x => x.cashierID == inputDto.cashierId);

            if (!string.IsNullOrWhiteSpace(inputDto.cashierName))
            {
                var cashierIds = db.Set<PosCashier>().AsNoTracking()
                    .Where(x => x.cashierName.Contains(inputDto.cashierName))
                    .Select(x => x.Id);
                posSalesFlows = posSalesFlows.Where(x => cashierIds.Contains(x.cashierID));
            }

            var goodInfos = db.Set<GoodsInfo>().AsNoTracking();
            var goodsCategorys = db.Set<GoodsCategory>().AsNoTracking();

            var result1 = await posSalesFlows.GroupJoin(goodInfos, ct => ct.goodsID, cs => cs.Id, (x, y) => new { posSalesFlows = x, goodsInfos = y })
                .SelectMany(xy => xy.goodsInfos.DefaultIfEmpty(), (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfo = y }).ToListAsync();
            var result = result1
                .GroupJoin(warehouseGoods, ct => $"{ct.posSalesFlows.warehouseID}{ct.posSalesFlows.goodsID}", cs => $"{cs.warehouseID}{cs.goodsID}", (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfos = x.goodsInfo, warehouseGoods = y })
                .SelectMany(xy => xy.warehouseGoods.DefaultIfEmpty(), (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfos = x.goodsInfos, warehouseGoods = y })
                //var result = await posSalesFlows.GroupJoin(goodInfos, ct => ct.goodsID, cs => cs.Id, (x, y) => new { posSalesFlows = x, goodInfos = y })
                //.SelectMany(xy => xy.goodInfos.DefaultIfEmpty(), (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfo = y })
                .GroupJoin(goodsCategorys, ct => ct.posSalesFlows.categoryID, cs => cs.Id, (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfos = x.goodsInfos, warehouseGoods = x.warehouseGoods, goodsCategorys = y })
                .SelectMany(xy => xy.goodsCategorys.DefaultIfEmpty(), (x, y) => new { posSalesFlows = x.posSalesFlows, goodsInfos = x.goodsInfos, warehouseGoods = x.warehouseGoods, goodsCategorys = y })
                .GroupBy(g => new
                {
                    g.posSalesFlows.warehouseID,
                    g.posSalesFlows.cashierID,
                    g.posSalesFlows.categoryID
                })
                .Select(s => new PosSaleCashierCategorySummaryStatisticsGoodShowDto
                {
                    warehouseID = s.Key.warehouseID,
                    cashierID = s.Key.cashierID,
                    categoryID = s.Key.categoryID,
                    categoryName = s.Max(y => y.goodsCategorys.categoryName),
                    categoryNo = s.Max(y => y.goodsCategorys.No),
                    saleQuantity = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleQuantity : 0),
                    saleMoney = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleMoney : 0),
                    reSaleQuantity = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Refunds ? y.posSalesFlows.saleQuantity : 0),
                    reSaleMoney = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Refunds ? y.posSalesFlows.saleMoney : 0),
                    totalSaleMoney = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleMoney : -y.posSalesFlows.saleMoney),
                    reGdAmount = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Refunds ? y.posSalesFlows.gdMoney : 0),
                    totalGdSaleMoney = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleMoney : -y.posSalesFlows.gdMoney),
                    totalSaleQuantity = s.Sum(y => y.posSalesFlows.saleWay == SalesTypes.Sales ? y.posSalesFlows.saleQuantity : -y.posSalesFlows.saleQuantity),
                    grossProfit = s.Sum(y => y.posSalesFlows.salePrice - (y.warehouseGoods == null ? 0 : y.warehouseGoods.costPrice))
                }).ToList();

            result = result.Where(x => x.categoryName != null).OrderBy(x => x.warehouseID).ThenBy(x => x.cashierID).ThenBy(x => x.categoryNo).ToList();

            await db.AttachData<PosCashier, PosSaleCashierCategorySummaryStatisticsGoodShowDto>(result, x => x.cashierID,
                (m, arr) => m.PosCashier = arr.FirstOrDefault());

            return result;
        }
        #endregion

        #endregion

        #region 导出销售流水数据 add by cl 20240508
        /// <summary>
        /// 导出销售流水数据
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public Stream ExportPosSalesFlowsInfo(PosSalesFlow[] data)
        {
            if (data.Length > 0)
            {
                var stream = new MemoryStream();
                using (var excel = new ExcelPackage())
                {
                    //sheet名称
                    var defaultSheet = excel.Workbook.Worksheets.Add($"销售流水报表");
                    defaultSheet.Cells["A1:R1"].Merge = true;
                    defaultSheet.Cells["A1:R1"].Value = string.Concat("销售流水");
                    defaultSheet.Cells["A1:R1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    defaultSheet.Cells["A1:R1"].Style.Font.Bold = true;
                    defaultSheet.Cells["A1:R1"].Style.Font.Size = 18;
                    defaultSheet.SetValue("A2", "流水号");
                    defaultSheet.SetValue("B2", "仓库");
                    defaultSheet.SetValue("C2", "供应商");
                    defaultSheet.SetValue("D2", "类别名称");
                    defaultSheet.SetValue("E2", "商品编号");
                    defaultSheet.SetValue("F2", "商品名称");
                    defaultSheet.SetValue("G2", "规格");
                    defaultSheet.SetValue("H2", "原价");
                    defaultSheet.SetValue("I2", "售价");
                    defaultSheet.SetValue("J2", "折扣金额");
                    defaultSheet.SetValue("K2", "销售数量");
                    defaultSheet.SetValue("L2", "销售金额");
                    defaultSheet.SetValue("M2", "改价差额");
                    defaultSheet.SetValue("N2", "实付金额");
                    defaultSheet.SetValue("O2", "销售方式");
                    defaultSheet.SetValue("P2", "会员卡号");
                    defaultSheet.SetValue("Q2", "收银员姓名");
                    defaultSheet.SetValue("R2", "操作时间");
                    defaultSheet.Cells["A2:R2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    defaultSheet.Cells["A2:R2"].Style.Font.Bold = true;
                    defaultSheet.Cells["A2:R2"].Style.Font.Size = 14;

                    for (int row = 3, i = 0; i < data.Length; i++)
                    {
                        var supplierName = string.Empty;
                        //data[i].goodsInfo.GoodsPrices?.ForEach(e => supplierName += (e.Supplier?.supplierName + ","));
                        var saleWay = data[i].saleWay == SalesTypes.Sales ? "销售" : data[i].saleWay == SalesTypes.Refunds ? "退货" : "其它";
                        defaultSheet.SetValue(row + i, 1, data[i].flowNo);
                        defaultSheet.SetValue(row + i, 2, data[i].Warehouse?.warehouseName);
                        defaultSheet.SetValue(row + i, 3, data[i].Supplier?.supplierName);
                        defaultSheet.SetValue(row + i, 4, data[i].GoodsCategory?.No + "-" + data[i].GoodsCategory?.categoryName);
                        defaultSheet.SetValue(row + i, 5, data[i].GoodsInfo.No);
                        defaultSheet.SetValue(row + i, 6, data[i].GoodsInfo.goodsName);
                        defaultSheet.SetValue(row + i, 7, data[i].GoodsInfo.specifications);
                        defaultSheet.SetValue(row + i, 8, data[i].sourcePrice);
                        defaultSheet.SetValue(row + i, 9, data[i].salePrice);
                        defaultSheet.SetValue(row + i, 10, data[i].discountMoney);
                        defaultSheet.SetValue(row + i, 11, data[i].saleQuantity);
                        defaultSheet.SetValue(row + i, 12, data[i].saleMoney);
                        defaultSheet.SetValue(row + i, 13, data[i].gapMoney);
                        defaultSheet.SetValue(row + i, 14, data[i].gdMoney);
                        defaultSheet.SetValue(row + i, 15, saleWay);
                        defaultSheet.SetValue(row + i, 16, data[i].PosMember?.memberTel);
                        defaultSheet.SetValue(row + i, 17, data[i].PosCashier?.cashierName);
                        defaultSheet.SetValue(row + i, 18, data[i].operateTime.ToString("yyyy-MM-dd HH:mm:ss"));

                        defaultSheet.Cells[row + i, 8].Style.Numberformat.Format = "#,##0.00";
                        defaultSheet.Cells[row + i, 9].Style.Numberformat.Format = "#,##0.00";
                        defaultSheet.Cells[row + i, 10].Style.Numberformat.Format = "#,##0.00";
                        defaultSheet.Cells[row + i, 11].Style.Numberformat.Format = "#,##0.00";
                        defaultSheet.Cells[row + i, 12].Style.Numberformat.Format = "#,##0.00";
                        defaultSheet.Cells[row + i, 13].Style.Numberformat.Format = "#,##0.00";
                        defaultSheet.Cells[row + i, 14].Style.Numberformat.Format = "#,##0.00";
                    }
                    defaultSheet.Cells[1, 1, data.Length + 3, 18].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    defaultSheet.Cells[1, 1, data.Length + 3, 18].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    defaultSheet.Cells[1, 1, data.Length + 3, 18].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    defaultSheet.Cells[1, 1, data.Length + 3, 18].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                    defaultSheet.Cells[1, 1, data.Length + 3, 18].Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                    defaultSheet.Cells[1, 1, data.Length + 3, 18].Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                    defaultSheet.Cells[1, 1, data.Length + 3, 18].Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
                    defaultSheet.Cells[1, 1, data.Length + 3, 18].Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                    //合计
                    defaultSheet.Cells[data.Length + 3, 1, data.Length + 3, 7].Merge = true;
                    defaultSheet.Cells[data.Length + 3, 1, data.Length + 3, 7].Value = "合计";
                    defaultSheet.Cells[data.Length + 3, 1, data.Length + 3, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    defaultSheet.Cells[data.Length + 3, 1, data.Length + 3, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    decimal sumSaleQuantity = 0.00M;
                    decimal sumSaleMoney = 0.00M;
                    decimal sumGapMoney = 0.00M;
                    decimal sumGdMoney = 0.00M;
                    foreach (var item in data)
                    {
                        sumSaleQuantity += item.saleWay == SalesTypes.Refunds ? -item.saleQuantity : item.saleQuantity;
                        sumSaleMoney += item.saleWay == SalesTypes.Refunds ? -item.saleMoney : item.saleMoney;
                        sumGapMoney += item.saleWay == SalesTypes.Refunds ? -item.gapMoney : item.gapMoney;
                        sumGdMoney += item.saleWay == SalesTypes.Refunds ? -item.gdMoney : item.gdMoney;
                    }
                    //defaultSheet.Cells[data.Length + 3, 7, data.Length + 3, 7].Value = sumCostPrice;
                    //defaultSheet.Cells[data.Length + 3, 8, data.Length + 3, 8].Value = sumPrice;
                    //defaultSheet.Cells[data.Length + 3, 9, data.Length + 3, 9].Value = sumFirstInQuantity;
                    defaultSheet.Cells[data.Length + 3, 10, data.Length + 3, 11].Value = sumSaleQuantity;
                    defaultSheet.Cells[data.Length + 3, 11, data.Length + 3, 12].Value = sumSaleMoney;
                    defaultSheet.Cells[data.Length + 3, 12, data.Length + 3, 13].Value = sumGapMoney;
                    defaultSheet.Cells[data.Length + 3, 13, data.Length + 3, 14].Value = sumGdMoney;

                    defaultSheet.Cells.AutoFitColumns();
                    excel.SaveAs(stream);
                }

                stream.Position = 0;
                return stream;
            }
            return null;
        }
        #endregion

        #region 根据会员号或手机号获取会员信息
        /// <summary>
        /// 根据会员号或手机号获取会员信息
        /// </summary>
        /// <param name="memberCardOrTel"></param>
        /// <returns></returns>
        public async Task<PosMember[]> GetPosMemberInfosByCardOrTel(string memberCardOrTel)
        {
            var db = await this.posSalesFlows.GetDbContextAsync();

            var posMembers = await db.Set<PosMember>().ToArrayAsync();
            posMembers = posMembers.Where(x => (x.memberTel != null && x.memberTel.Contains(memberCardOrTel)) || (x.No != null && x.No.Contains(memberCardOrTel))).ToArray();

            return posMembers;
        }

        #endregion
    }
}
